﻿ IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_BrowseContactUs')
	BEGIN
		DROP  Procedure  [esf_sso].P_BrowseContactUs
	END

GO

CREATE Procedure [esf_sso].P_BrowseContactUs
	@pUserAlias				VARCHAR (20) = NULL
	,@pUserFullName			VARCHAR (MAX) = NULL
	,@pUserIdentificationNumber			VARCHAR (MAX) = NULL
	,@pUserIdentificationTypeID			INT = NULL
	,@pSenderEmail			VARCHAR (150) = NULL
	,@pMessageStartDate		DATETIME = NULL
	,@pMessageEndDate		DATETIME = NULL
	,@pContactUsTypeID		INT = NULL
	,@pWithResponse			BIT = NULL

	,@pPageIndex			BIGINT = 0
	,@pPageSize				BIGINT = 30

	,@pExtraValue			VARCHAR (MAX) = NULL
AS

	SELECT * FROM 
		(SELECT 
			ROW_NUMBER () OVER (ORDER BY CU.CreationDateTime DESC) AS Ri
			,COUNT(*) OVER () AS RCount
			,CU.*
			,VU.Usr_Alias	calUserAlias
			,VCT.ID		Cut_ID
			,VCT.Code	Cut_Code
			,VCT.Name	Cut_Name
		FROM
			esf_sso.ContactUs CU
		LEFT JOIN
			esf_sso.VUser VU ON VU.Usr_Per_Email = CU.SenderEmail
		LEFT JOIN
			esf_sso.VContactUsType VCT ON VCT.ID = CU.ContactUsTypeID
		WHERE
			(@pUserAlias IS NULL OR VU.Usr_Alias LIKE @pUserAlias)
			AND (@pUserFullName IS NULL OR VU.Usr_Per_cal_FullName LIKE @pUserFullName)
			AND (@pUserIdentificationNumber IS NULL OR VU.Usr_Per_IdentificationNumber LIKE @pUserIdentificationNumber)
			AND (@pUserIdentificationTypeID IS NULL OR VU.Usr_Per_IT_ID = @pUserIdentificationTypeID)
			AND (@pUserFullName IS NULL OR VU.Usr_Per_cal_FullName LIKE @pUserFullName)
			AND (@pSenderEmail IS NULL OR VU.Usr_Per_Email LIKE @pSenderEmail)
			AND (@pMessageStartDate IS NULL OR @pMessageStartDate >= CU.CreationDateTime)
			AND (@pMessageEndDate IS NULL OR @pMessageEndDate <= CU.CreationDateTime)
			AND (@pContactUsTypeID IS NULL OR ContactUsTypeID = @pContactUsTypeID)
			AND (@pWithResponse IS NULL OR (@pWithResponse = 1 AND LEN (CU.Response) > 0) OR (@pWithResponse = 0 AND CU.Response IS NULL))
			-- Ajustado por cliente, BasicReferenceTypeID = 53-Codigos Institucionales
			AND (@pExtraValue IS NULL OR @pExtraValue = (SELECT TOP 1 UBR.BasicReferenceID FROM esf_sso.User_BasicReference UBR INNER JOIN esf_sso.BasicReference BR ON BR.ID = BasicReferenceID WHERE UBR.UserPersonID = VU.Usr_Per_ID AND BR.BasicReferenceTypeID = 53))
		) T
		WHERE
			T.Ri BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
	ORDER BY 
		T.Ri
GO